# -*- coding: utf-8 -*-
# @Time    : 2024/03/21 09:44
# @Author  : Jonny
# @File    : LuckMqMsgService.py
# @Describe: MQ消息存储逻辑服务层

import uuid
import time
import json
import os
from LuckDbSqliteHelperUtils import LuckDbSqliteHelperUtils
from LuckMqConstant import LuckMqConstant

class LuckMqMsgService(object):

    def __init__(self, config):
        self.config = config

    # 创建表
    def create_table(self):
        script_dir = os.path.dirname(os.path.abspath(__file__))  # 项目工程路径/app
        sqlite_helper = LuckDbSqliteHelperUtils(
            os.path.join(script_dir, LuckMqConstant.DB_DIR, LuckMqConstant.DB_NAME))
        with sqlite_helper as um:
            um.cursor.execute(
                '''create table luck_mq_msg (id text, regt text, regid text, type text, state text, userid text, mq_name text, mq_url text, mq_config text, vhost text, note text, name text, descr text)''')

        result = {
            "code": 1,
            "msg": "创建表成功"
        }
        return result

    # 清空数据
    def delete_table(self):
        script_dir = os.path.dirname(os.path.abspath(__file__))  # 项目工程路径/app
        sqlite_helper = LuckDbSqliteHelperUtils(
            os.path.join(script_dir, LuckMqConstant.DB_DIR, LuckMqConstant.DB_NAME))
        with sqlite_helper as um:
            um.cursor.execute('delete from luck_mq_msg')

        result = {
            "code": 1,
            "msg": "清空数据成功"
        }
        return result

    # 删除表
    def drop_table(self):
        script_dir = os.path.dirname(os.path.abspath(__file__))  # 项目工程路径/app
        sqlite_helper = LuckDbSqliteHelperUtils(
            os.path.join(script_dir, LuckMqConstant.DB_DIR, LuckMqConstant.DB_NAME))
        with sqlite_helper as um:
            um.cursor.execute('drop table luck_mq_msg')
        result = {
            "code": 1,
            "msg": "删除表成功"
        }
        return result

    # 读取表数据
    def read_table_data(self, parameters):
        if "tablename" not in parameters or ("tablename" in parameters and (len(parameters["tablename"]) <= 0)):
            return {
                "code": 0,
                "msg": "tablename不能为空"
            }
        tablename = parameters["tablename"]
        del parameters["tablename"]
        sql = f"select a.* from {tablename} a where 1=1 "
        # 遍历parameters字典，并构建WHERE子句的条件
        conditions = []
        for key, value in parameters.items():
            if value is not None:
                conditions.append(f"a.{key} = '{value}'")
        # 如果存在条件，将它们用AND连接起来，并添加到SQL查询中
        if conditions:
            sql += " AND " + " AND ".join(conditions)
        sql += " order by regt desc "
        print(f"查询表{tablename}语句：{sql}")

        script_dir = os.path.dirname(os.path.abspath(__file__))  # 项目工程路径/luck
        db_helper = LuckDbSqliteHelperUtils(os.path.join(script_dir, LuckMqConstant.DB_DIR, LuckMqConstant.DB_NAME))
        with db_helper as um:
            um.cursor.execute(sql)
            # 获取列名
            columns = [description[0] for description in um.cursor.description]

            # 初始化一个空列表来保存结果
            result_list = []

            # 遍历查询结果
            for row in um.cursor.fetchall():
                # 创建一个字典，其中键是列名，值是数据
                row_dict = {col: value for col, value in zip(columns, row)}
                # 将字典添加到列表中
                result_list.append(row_dict)

            print(f"查询表{tablename}条数：{len(result_list)}，数据：{result_list}")
            return {
                "code": 1,
                "msg": "操作成功",
                "data": result_list
            }

    # 添加消息
    def add(self, parameters):
        parameters["id"] = str(uuid.uuid4())
        parameters["regt"] = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))
        parameters["regid"] = "小程序"
        parameters["state"] = 0  # 发送状态（0未发送/1已发送）

        # 构建字段名和参数列表
        fields = []
        values = []
        for key, value in parameters.items():
            if value is not None:
                fields.append(key)
                if isinstance(value, dict):
                    # 如果value是dict类型，先将其转换为json字符串
                    values.append(json.dumps(value, ensure_ascii=False))
                else:
                    # 对于非dict类型，直接添加value
                    values.append(value)

                    # 构建SQL模板
        placeholders = ', '.join(['?'] * len(fields))
        sql_template = f"INSERT INTO luck_mq_msg ({', '.join(fields)}) VALUES ({placeholders})"

        # 输出日志（使用占位符以避免日志中的SQL注入风险）
        logging_placeholders = ', '.join(['%s'] * len(fields))
        logging_sql = f"添加消息语句：INSERT INTO luck_mq_msg ({', '.join(fields)}) VALUES ({logging_placeholders})"
        print(logging_sql % tuple(values))

        script_dir = os.path.dirname(os.path.abspath(__file__))  # 项目工程路径/luck
        db_helper = LuckDbSqliteHelperUtils(os.path.join(script_dir, LuckMqConstant.DB_DIR, LuckMqConstant.DB_NAME))
        with db_helper as um:
            um.cursor.execute(sql_template, values)

        list = self.read({
            "id": parameters["id"]
        })
        return {
            "code": 1,
            "msg": "操作成功",
            "data": list["data"][0]
        }

    # 修改消息
    def update(self, parameters):
        if "id" not in parameters or ("id" in parameters and len(parameters["id"]) <= 0):
            return {
                "code": 0,
                "msg": "id不能为空"
            }
        id = parameters["id"]

        # 遍历parameters字典，并构建SET子句的条件
        conditions = []
        for key, value in parameters.items():
            if value is not None:
                if isinstance(value, dict):
                    value = json.dumps(value, ensure_ascii=False)
                conditions.append(f"{key} = '{value}'")
        # 如果存在条件，将它们用英文逗号连接起来，并添加到SQL查询中
        sql_update = ""
        if conditions:
            sql_update = ", ".join(conditions)
        sql = f"update luck_mq_msg set {sql_update} where id='{id}'"
        print(f"修改消息语句：{sql}")

        script_dir = os.path.dirname(os.path.abspath(__file__))  # 项目工程路径/luck
        db_helper = LuckDbSqliteHelperUtils(os.path.join(script_dir, LuckMqConstant.DB_DIR, LuckMqConstant.DB_NAME))
        with db_helper as um:
            um.cursor.execute(sql)

        list = self.read({
            "id": id
        })
        return {
            "code": 1,
            "msg": "操作成功",
            "data": list["data"][0]
        }

    # 查询消息
    def read(self, parameters):
        sql = "select a.* from luck_mq_msg a where 1=1 "
        # 遍历parameters字典，并构建WHERE子句的条件
        conditions = []
        for key, value in parameters.items():
            if value is not None:
                conditions.append(f"a.{key} = '{value}'")
        # 如果存在条件，将它们用AND连接起来，并添加到SQL查询中
        if conditions:
            sql += " AND " + " AND ".join(conditions)
        sql += " order by regt desc "
        print(f"查询消息语句：{sql}")

        script_dir = os.path.dirname(os.path.abspath(__file__))  # 项目工程路径/luck
        db_helper = LuckDbSqliteHelperUtils(os.path.join(script_dir, LuckMqConstant.DB_DIR, LuckMqConstant.DB_NAME))
        with db_helper as um:
            um.cursor.execute(sql)
            # 获取列名
            columns = [description[0] for description in um.cursor.description]

            # 初始化一个空列表来保存结果
            result_list = []

            # 遍历查询结果
            for row in um.cursor.fetchall():
                # 创建一个字典，其中键是列名，值是数据
                row_dict = {col: value for col, value in zip(columns, row)}
                # 将字典添加到列表中
                result_list.append(row_dict)

            print(f"查询消息条数：{len(result_list)}，数据：{result_list}")
            return {
                "code": 1,
                "msg": "操作成功",
                "data": result_list
            }

    # 删除消息
    def delete(self, parameters):
        # 检查parameters是否包含有效的删除条件
        if not parameters or not any(parameters.values()):
            return {
                "code": 0,
                "msg": "缺少必要的删除条件",
                "data": None
            }

        # 初始化WHERE子句和参数列表
        where_clause = []
        values = []

        # 动态构建WHERE子句和参数列表
        for key, value in parameters.items():
            if value is not None:
                where_clause.append(f"{key} = ?")
                values.append(value)

                # 拼接WHERE子句
        where_str = " AND ".join(where_clause)
        if where_str:
            where_str = "WHERE " + where_str

        # 构建SQL删除语句
        sql_template = f"DELETE FROM luck_mq_msg {where_str}"

        # 输出日志（使用占位符以避免SQL注入风险）
        logging_placeholders = ', '.join(['%s'] * len(values)) if values else ''
        logging_sql = f"删除消息语句：{sql_template} 值：{logging_placeholders}"
        print(logging_sql % tuple(values))

        # 执行删除操作
        script_dir = os.path.dirname(os.path.abspath(__file__))
        db_helper = LuckDbSqliteHelperUtils(os.path.join(script_dir, LuckMqConstant.DB_DIR, LuckMqConstant.DB_NAME))
        with db_helper as um:
            um.cursor.execute(sql_template, values)

        return {
            "code": 1,
            "msg": "操作成功"
        }
